﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SQLiteTutorial
{
    class Program
    {
        const string kDataSource = "Data Source=test.s3db";

        static void Main(string[] args)
        {           
            ConsoleKeyInfo s;
            do
            {
                printMenu();
                s = Console.ReadKey(true);
                choose(s);
            } while (s.KeyChar != 'q');            
        }

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(kDataSource);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch ( Exception e )
            {
                Console.WriteLine("EXCEPTION : " + e.Message);
                // Catching exceptions is for communists
            }
            return dt;
        }

        public static int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(kDataSource);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = sql;
                rowsUpdated = mycommand.ExecuteNonQuery();
                cnn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("EXCEPTION : " + e.Message);
            }
            return rowsUpdated;
        }

        public static string ExecuteScalar(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection(kDataSource);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(cnn);
            mycommand.CommandText = sql;
            object value = mycommand.ExecuteScalar();
            cnn.Close();
            if (value != null)
            {
                return value.ToString();
            }
            return "";
        }

        public static void choose(ConsoleKeyInfo s)
        {
            switch (s.KeyChar)
            {
                case '1' :
                    addAction();
                    break;
                case '2':
                    removeAction();
                    break;
                case '3':
                    updateAction();
                    break;
                case '4':
                    sortAction();
                    break;
                case '5' :
                    printTable(GetDataTable("SELECT * FROM zdarzenia"));
                    break;
            }
        }

        public static void chooseSort(ConsoleKeyInfo s)
        {
            switch (s.KeyChar)
            {
                case 'a':
                    printTable(GetDataTable("SELECT * FROM zdarzenia ORDER BY zdarzenie"));
                    break;
                case 'b':
                    printTable(GetDataTable("SELECT * FROM zdarzenia ORDER BY time"));
                    break;
                case 'c':
                    printTable(GetDataTable("SELECT * FROM zdarzenia ORDER BY kto"));
                    break;                
            }
        }

        public static void printTable(DataTable table)
        {
            foreach (DataRow row in table.Rows) // Loop over the rows.
            {
                Console.WriteLine("------"); // Print separator.
                foreach (var item in row.ItemArray) // Loop over the items.
                {
                    //Console.Write("Item: "); // Print label.
                    Console.WriteLine(item); // Invokes ToString abstract method.
                }
            }
            Console.WriteLine();
        }

        public static void addAction()
        {
            Console.Write("ACTION ID ? ");
            string actionId = Console.ReadLine();
            Console.Write("WHO (A13, B12) ? ");
            string who = Console.ReadLine();
            string sql = "INSERT INTO zdarzenia (zdarzenie, kto) VALUES (" + actionId + ", '" + who + "')";
            Console.WriteLine("ROWS AFFECTED: " + ExecuteNonQuery(sql));
            Console.WriteLine();
        }

        public static void removeAction()
        {
            Console.Write("remove by id ");
            string actionId = Console.ReadLine();
            string sql = "DELETE FROM zdarzenia WHERE id=" + actionId; 
            Console.WriteLine("ROWS AFFECTED: " + ExecuteNonQuery(sql));
            Console.WriteLine();
        }

        public static void updateAction()
        {
            Console.Write("update by id ");
            string id = Console.ReadLine();
            Console.Write("INSERT NEW VALUES : \nACTION ID ? ");
            string actionId = Console.ReadLine();
            Console.Write("WHO (A13, B12) ? ");
            string who = Console.ReadLine();
            string sql = "UPDATE zdarzenia SET zdarzenie=" + actionId + ", kto='" + who + "' WHERE id=" + id;
            Console.WriteLine("ROWS AFFECTED: " + ExecuteNonQuery(sql));
            Console.WriteLine();
        }

        public static void sortAction()
        {
            ConsoleKeyInfo s;
            do
            {
                printSubMenu4();
                s = Console.ReadKey(true);
                chooseSort(s);
            } while (s.KeyChar != 'q');                
        }       

        public static void printMenu()
        {
            Console.WriteLine("MENU :");
            Console.WriteLine(" 1) ADD ACTION");
            Console.WriteLine(" 2) REMOVE ACTION");
            Console.WriteLine(" 3) UPDATE ACTION");
            Console.WriteLine(" 4) SORT ACTIONS");
            Console.WriteLine(" 5) PRINT ALL ACTIONS");
            Console.WriteLine(" q) QUIT");
            Console.WriteLine(" ");
        }

        public static void printSubMenu4()
        {
            Console.WriteLine(" a) SORT BY ACTION");
            Console.WriteLine(" b) SORT BY TIME");
            Console.WriteLine(" c) SORT BY WHO");            
            Console.WriteLine(" q) QUIT");
        }
    }
}
